﻿using Microsoft.AspNetCore.Mvc;
using mvc.Models.Database;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace mvc.Controllers
{
    //[Route("[controller]/[action]")]
   // [Route("/api/[controller]/[action]")]
    public class TestController : Controller
    {
        [Route("/")]
        public IActionResult Index()
        {
            return View();
        }

        public IActionResult myView()
        {
            return View();
        }

        private readonly Store_2022Context _db;
        public TestController(Store_2022Context db)
        {
            _db = db;
        }

        public IActionResult AddTEST()
        {
            //1、new一个分类对象 
            var cate = new Category()
            {
                CateName = "手机"
            };

            //2、添加数据
            _db.Categories.Add(cate);

            //3、保存 执行SQL
            var row = _db.SaveChanges();
            return Content("添加数据");
        }
        public IActionResult AddGoods()
        {
            var cate = new Good()
            {
                Name = "冰箱",
                Cover="",
                Price=2999,
                Stock=30,
                CateId=2,
                CreateTime=DateTime.Now
            };

            //2、添加数据
            _db.Goods.Add(cate);

            //3、保存 执行SQL
            var row = _db.SaveChanges();
            return Content("添加数据");
        }
        public IActionResult GetGoods()
        {
            var list = _db.Goods.Where(x => x.CateId == 1).ToList();
            return new ObjectResult(list);
        }
        public IActionResult UpdataGoods()
        {
            var list = _db.Goods.Where(x => x.CateId ==2).ToList();
            foreach (var item in list)
            {
                item.CateId =4;
            }
            _db.Goods.UpdateRange(list);
            var row = _db.SaveChanges();
            if (row > 0)
                return Content("添加成功");
            return Content("添加失败");
        }
        public IActionResult DeleteGoods()
        {
            var model = _db.Goods.Where(x => x.Price == 1899).FirstOrDefault();
            if (model == null)
            {
                return Content("数据不存在");
            }
            _db.Goods.Remove(model);
            var row = _db.SaveChanges();
            if (row > 0)
                return Content("删除成功");
            return Content("删除失败");
        }
        public IActionResult SoftGoods()
        {
            /*在Test控制器添加SoftGoods方法，查询商品数据并以分类Id倒序显示*/
            var list = _db.Goods.OrderByDescending(x => x.CateId).ToList();
            return new ObjectResult(list);
        }
        public IActionResult SkipGoods()
        {
            //在Test控制器添加SkipGoods方法，查询以商品金额倒序的3 - 7个商品
            var list1 = _db.Goods.OrderByDescending(x => x.Price).Skip(2).Take(5).ToList();
            return new ObjectResult(list1);         
        }
        public IActionResult AnyGoods()
        {
            //在Test控制器添加AnyGoods方法，判断商品金额为100的商品是否存在
            var result = _db.Goods.Any(x => x.Price == 100);
            if(result)
            return Content("存在");
            return Content("不存在");
        }
        public IActionResult CountGoods()
        {
            //在Test控制器添加CountGoods方法，查询商品金额为500 - 1000的商品数量
            var count = _db.Goods.Count(x => x.Price >= 500 && x.Price <= 1000);
            return new ObjectResult(count);
        }

        public IActionResult SumGoods()
        {        
            //在Test控制器添加SumGoods方法，查询分类Id为“1”的商品总额
            var sum = _db.Goods.Where(x => x.CateId == 1).Sum(x => x.Price);
            return new ObjectResult(sum);
        }
        public IActionResult StockGoods()
        {
            //在Test控制器添加StockGoods方法，统计每个分类的总库存
            var list = _db.Goods.GroupBy(x => x.CateId).Select(x => new
            {
                x.Key,
                sum=x.Sum(z => z.Stock)
            }).ToList();
            return new ObjectResult(list);
        }
        public IActionResult JoinGoods()
        {
            //在Join控制器添加JoinGoods方法名，查询分类名称为“数码”的商品数据
            var list = _db.Goods.Join(_db.Categories, x => x.CateId, y => y.Id,
                (x, y) => new
                {
                    x.Id,
                    x.Name,
                    y.CateName
                }).Where(y => y.CateName == "数码").ToList();
            return new ObjectResult(list);
        }
    }



}
